package com.lanyou.esb.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.data.rest.core.annotation.RestResource;

import com.lanyou.esb.entity.Token;

/**
 * 
 * @author Davey.wu
 */
public interface TokenRepository extends
		Repository<Token, String>,
		JpaSpecificationExecutor<Token> {
	/**
	 * 根据接口标识和用户编码获取令牌信息
	 * 
	 * @param ifId
	 *            ：接口标识
	 * @param userCode
	 *            ：用户编码
	 * @return
	 */
	@Query(nativeQuery = true,
			value = "SELECT "
			+ "t.if_token_id AS tokenId, " 
			+ "t.app_key AS appKey, " 
			+ "h.if_http_id AS ifId, " 
			+ "t.if_call_system_code AS callSysCode, " 
			+ "(SELECT f.system_name FROM t_cook_bu_field_system f WHERE f.field_system_id=t.IF_CALL_SYSTEM_ID) AS callSystemName  "
			+ "FROM t_esb_bu_if_token t " 
			+ "INNER JOIN t_esb_bu_if_http h ON t.if_id=h.if_http_id "
			+ "WHERE h.if_http_id=:ifId "
			+ "AND EXISTS( "
			+ "	SELECT * FROM t_cook_bu_field_system f "
			+ "	INNER JOIN t_cook_bu_user_system us ON f.field_system_id=us.field_system_id " 
			+ "	INNER JOIN t_cook_db_user u ON us.user_id=u.user_id " 
			+ "	WHERE f.FIELD_SYSTEM_ID=t.IF_CALL_SYSTEM_ID " 
			+ "	AND u.user_code=:userCode ) ")
	List<Object[]> findTokenByIfIdAndUserCode(@Param("ifId") String ifId,
			@Param("userCode") String userCode);
	
	@Query(nativeQuery = true,
			value = 
			 "SELECT * FROM t_esb_bu_if_token"+
			 " WHERE IF_ID = :interfaceId"
		)
	List<Token> findByInterfaceId(@Param("interfaceId")String interfaceId);
	
	@Query(nativeQuery = true,
			value = 
					"SELECT * FROM  (SELECT t5.FIELD_NAME,t5.FIELD_ID,t4.SYSTEM_NAME,t4.FIELD_SYSTEM_ID,f.IF_NAME,f.IF_HTTP_ID,GROUP_CONCAT(t3.SYSTEM_NAME) union_SYSTEM_NAME FROM "
					+ " (SELECT * FROM `t_esb_bu_if_http` t1 WHERE EXISTS"
					+ " (SELECT user_id FROM `t_cook_bu_user_system` t6 WHERE USER_ID = :userId and t1.FIELD_SYSTEM_ID = t6.FIELD_SYSTEM_ID)"
					+ ") f"
					+ " LEFT JOIN t_esb_bu_if_token t2 ON t2.IF_ID = F.IF_HTTP_ID"
					+ " LEFT JOIN t_cook_bu_field_system  t3 ON t3.FIELD_SYSTEM_ID = t2.IF_CALL_SYSTEM_ID"
					+ " LEFT JOIN t_cook_bu_field_system  t4 ON t4.FIELD_SYSTEM_ID = f.FIELD_SYSTEM_ID"
					+ " LEFT JOIN t_cook_db_field t5 ON t5.FIELD_ID = t4.FIELD_ID"
					+ " GROUP BY t5.FIELD_NAME,t5.FIELD_ID,t4.SYSTEM_NAME,t4.FIELD_SYSTEM_ID,f.IF_NAME,f.IF_HTTP_ID) t"
					+" WHERE(''=:systemId OR t.FIELD_SYSTEM_ID = :systemId)"
					+" AND (''=:fieldId OR t.FIELD_ID = :fieldId)"
					+" AND (''=:interfaceName OR t.IF_NAME like :interfaceName)"
					+" AND (''=:appSystemName OR t.union_SYSTEM_NAME like :appSystemName)"
					+ "LIMIT :page1,:page2"
		)
	List<Object[]> findForRouting(@Param("interfaceName")String interfaceName,
								@Param("fieldId") String fieldId,
								@Param("systemId") String systemId,
								@Param("appSystemName") String appSystemName,
								@Param("userId") String userId,
								@Param("page1") int page1,
								@Param("page2") int page2
								);
	@Query(nativeQuery = true,
			value = 
					"SELECT * FROM  (SELECT t5.FIELD_NAME,t5.FIELD_ID,t4.SYSTEM_NAME,t4.FIELD_SYSTEM_ID,f.IF_NAME,f.IF_HTTP_ID,GROUP_CONCAT(t3.SYSTEM_NAME) union_SYSTEM_NAME FROM "
							+ " (SELECT * FROM `t_esb_bu_if_http` t1 WHERE EXISTS"
							+ " (SELECT user_id FROM `t_cook_bu_user_system` t6 WHERE USER_ID = :userId and t1.FIELD_SYSTEM_ID = t6.FIELD_SYSTEM_ID)"
							+ ") f"
							+ " LEFT JOIN t_esb_bu_if_token t2 ON t2.IF_ID = F.IF_HTTP_ID"
							+ " LEFT JOIN t_cook_bu_field_system  t3 ON t3.FIELD_SYSTEM_ID = t2.IF_CALL_SYSTEM_ID"
							+ " LEFT JOIN t_cook_bu_field_system  t4 ON t4.FIELD_SYSTEM_ID = f.FIELD_SYSTEM_ID"
							+ " LEFT JOIN t_cook_db_field t5 ON t5.FIELD_ID = t4.FIELD_ID"
							+ " GROUP BY t5.FIELD_NAME,t5.FIELD_ID,t4.SYSTEM_NAME,t4.FIELD_SYSTEM_ID,F.IF_NAME,F.IF_HTTP_ID) t"
					+" WHERE(''=:systemId OR t.FIELD_SYSTEM_ID = :systemId)"
					+" AND (''=:fieldId OR t.FIELD_ID = :fieldId)"
					+" AND (''=:interfaceName OR t.IF_NAME like :interfaceName)"
					+" AND (''=:appSystemName OR t.union_SYSTEM_NAME like :appSystemName)"
		)
	List<Object[]> countForRouting(@Param("interfaceName")String interfaceName,
								@Param("fieldId") String fieldId,
								@Param("systemId") String systemId,
								@Param("appSystemName") String appSystemName,
								@Param("userId") String userId
								);
	
	long count();

	boolean exists(String id);
	
	@RestResource(exported = true)
	<S extends Token> S save(S entity); // could expose REST API

	<S extends Token> List<S> save(Iterable<S> entities);

	@RestResource(exported = true)
	// @RequiresPermissions(value={"HttpManagement:view","HttpManagement:maintain"},logical=Logical.OR)
	Token findOne(String id); // could expose REST API

	Token getOne(String id);

	// 以下三个接口只能选一个暴露为REST API

	@RestResource(exported = false)
	List<Token> findAll(); // could expose REST API

	@RestResource(exported = false)
	List<Token> findAll(Sort sort); // could expose REST API

	@RestResource(exported = true)
	// @RequiresPermissions(value={"HttpManagement:view","HttpManagement:maintain"},logical=Logical.OR)
	Page<Token> findAll(Pageable pageable); // could expose REST API

	List<Token> findAll(Iterable<String> ids);

	// 以下两个接口，要么都暴露为REST API，要么都不暴露
	@RestResource(exported = true)
	// @RequiresPermissions(value={"HttpManagement:maintain"})
	void delete(String id); // could expose REST API

	@RestResource(exported = true)
	// @RequiresPermissions(value={"HttpManagement:maintain"})
	void delete(Token entity); // could expose REST API

	void delete(Iterable<? extends Token> entities);

	void deleteAll();

	void flush();

	<S extends Token> S saveAndFlush(S entity);

	void deleteInBatch(Iterable<Token> entities);

	void deleteAllInBatch();

}
